﻿#region Copyright (C) Sector7 Software Studio. All rights reserved
/*******************************************************************************************
 * Creation
 *      Author:         Wolf Lai
 *      Date:           2013/2/18 星期一 23:54:38
 *      Version:        
 * Modification 
 *      Author: 
 *      Date: 
 *      Description: 
 *      Version:
 *******************************************************************************************/
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;
using Sector7.Framework.Core;
using Sector7.Framework.Enums;
using System.IO;
using Sector7.Framework.Utility;
using Sector7.Framework.Consts;

namespace Sector7.Framework.DB
{
    /// <summary>
    /// 数据库工具类
    /// </summary>
    public class DBUtil
    {
        #region Public functions
        /// <summary>
        /// 读取数据填充至实体对象
        /// </summary>
        /// <param name="reader">数据库只读流对象</param>
        /// <param name="obj">实体对象引用</param>
        [Obsolete("该方法已被另一同名方法替代。", false)]
        public static void ReadData(SqlDataReader reader, ref object obj)
        {
            string propertyName = string.Empty;
            object propertyValue = null;
            Type valueType = null;
            PropertyInfo property = null;

            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);

            for (int i = 0; i < reader.FieldCount; ++i)
            {
                propertyName = reader.GetName(i);
                propertyValue = values[i];
                valueType = propertyValue.GetType();

                if (valueType != typeof(DBNull))
                {
                    property = obj.GetType().GetProperty(propertyName);

                    if (property != null)
                    {
                        if (valueType.Name == "String")
                            propertyValue = propertyValue.ToString().Trim();
                        property.SetValue(obj, propertyValue, null);
                    }
                }
            }
        }

        /// <summary>
        /// 读取数据填充至实体对象
        /// </summary>
        /// <param name="reader">数据库只读流对象</param>
        /// <param name="obj">实体对象</param>
        public static void ReadData(SqlDataReader reader, object obj)
        {
            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);

            for (int i = 0; i < reader.FieldCount; ++i)
            {
                ReflectionUtil.SetPropertyValue(obj, reader.GetName(i), values[i]);
            }
        }

        /// <summary>
        /// 创建数据参数
        /// </summary>
        /// <param name="value">值</param>
        /// <param name="paramName">预置参数名称</param>
        /// <returns></returns>
        public static SqlParameter CreateSqlParameter(object value, string paramName)
        {
            SqlParameter param = new SqlParameter();

            param.Value = (value == null) ? DBNull.Value : value;
            param.ParameterName = paramName;

            return param;
        }

        /// <summary>
        /// 创建数据参数
        /// </summary>
        /// <param name="value">值</param>
        /// <returns></returns>
        public static SqlParameter CreateSqlParameter(object value)
        {
            SqlParameter param = new SqlParameter();

            param.Value = (value == null) ? DBNull.Value : value;

            return param;
        }

        /// <summary>
        /// 批量创建数据参数
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static SqlParameter[] CreateSqlParameterArray(object entity, params string[] columns)
        {
            var paramList = new List<SqlParameter>();

            foreach (var column in columns)
            {
                var param = CreateSqlParameter(
                    ReflectionUtil.GetPropertyValue(entity, column),
                    column);

                paramList.Add(param);
            }

            return paramList.ToArray();
        }

        /// <summary>
        /// 批量创建数据参数
        /// </summary>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static SqlParameter[] CreateSqlParameterArray(params EntityConditionBase[] conditions)
        {
            var paramList = new List<SqlParameter>();

            foreach (var condition in conditions)
            {
                var param = CreateSqlParameter(condition.ConditionValue, condition.ConditionName);
                paramList.Add(param);
            }

            return paramList.ToArray();
        }

        /// <summary>
        /// 创建带方向的数据参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="paramType">参数数据类型</param>
        /// <param name="paramDirection">参数方向</param>
        /// <returns></returns>
        public static SqlParameter CreateDirectionParameter(string paramName, DbType paramType, ParameterDirection paramDirection)
        {
            SqlParameter param = new SqlParameter();

            param.ParameterName = paramName;
            param.Direction = paramDirection;
            param.DbType = paramType;

            return param;
        }

        /// <summary>
        /// 映射数据类型
        /// </summary>
        /// <param name="type">值类型</param>
        /// <returns></returns>
        public static DbType MappingDbType(Type type)
        {
            DbType dbType = DbType.String;

            switch (type.ToString())
            {
                case "":
                    dbType = DbType.String;
                    break;
                default:
                    break;
            }

            return dbType;
        }

        /// <summary>
        /// 创建插入SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="isReturnPk"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static string CreateInsertSql(string tableName, bool isReturnPk, params string[] columns)
        {
            string columnNames = string.Empty;
            string columnValues = string.Empty;
            for (int i = 0; i < columns.Length; i++)
            {
                columnNames += columns[i];
                columnValues += string.Format("@{0}", columns[i]);
                if (i < columns.Length - 1)
                {
                    columnNames += ",";
                    columnValues += ",";
                }
            }

            string sql = string.Format(@"
                INSERT INTO {0}
                        ({1})
                    VALUES
                        ({2})",
                tableName, columnNames, columnValues);

            if (isReturnPk)
            {
                sql += @"
                    SELECT @@IDENTITY";
            }

            return sql;
        }

        /// <summary>
        /// 创建查询SQL语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="conditions"></param>
        /// <returns></returns>
        public static string CreateQuerySql(string tableName, params EntityConditionBase[] conditions)
        {
            string sql = string.Format("SELECT * FROM {0}", tableName);

            for (int i = 0; i < conditions.Length; i++)
            {
                if (i == 0)
                {
                    sql += " WHERE ";
                }

                sql += string.Format(" {0} = @{0}", conditions[i].ConditionName);

                if (i < conditions.Length - 1)
                {
                    sql += " AND ";
                }
            }

            return sql;
        }

        /// <summary>
        /// 创建更新SQL语句
        /// </summary>
        /// <param name="delta">改变量</param>
        /// <returns></returns>
        public static string CreateUpdateSql(EntityDeltaBase delta)
        {
            try
            {
                string setString = CreateEqualsSql(delta.DeltaProperties);
                string conditionString = CreateEqualsSql(delta.Conditions);

                //int repeatCount = 0;
                //Dictionary<string, string> newKyes = new Dictionary<string, string>();
                //int i = 0;
                //foreach (KeyValuePair<string, object> kvp in delta.Conditions)
                //{
                //    Log.WriteLine(LogSeverityType.DBG, "The {0} times loop.",
                //        i);
                //    string key = kvp.Key;
                //    if (delta.DeltaProperties.ContainsKey(key))
                //    {
                //        repeatCount++;
                //        string newKey = string.Format("{0}_{1}", key, repeatCount);

                //        conditionString = conditionString.Replace(
                //            string.Format("@{0}", key),
                //            string.Format("@{0}", newKey));

                //        newKyes.Add(key, newKey);

                //        Log.WriteLine(LogSeverityType.DBG, "REPEATED!!! The key is {0}, The new key is {1}.",
                //            key,
                //            newKey);
                //    }
                //    i++;
                //}

                //foreach (KeyValuePair<string, string> kvp in newKyes)
                //{
                //    object value = delta.Conditions[kvp.Key];
                //    delta.Conditions.Add(kvp.Value, value);
                //    delta.Conditions.Remove(kvp.Key);
                //}

                string sql = string.Format("UPDATE {0} SET {1} WHERE {2}",
                    delta.EntityName,
                    setString,
                    conditionString);

                return sql;
            }
            catch (Exception e)
            {
                Log.WriteLine(LogSeverityType.ERR, C_ModuleName.FRAMEWORK, e);
                throw e;
            }
        }

        /// <summary>
        /// 创建更新SQL语句中使用的参数集
        /// </summary>
        /// <param name="delta"></param>
        /// <returns></returns>
        public static SqlParameter[] CreateUpdateParameters(EntityDeltaBase delta)
        {
            SqlParameter[] parameters = new SqlParameter[delta.Conditions.Count + delta.DeltaProperties.Count];

            int i = 0;
            foreach (KeyValuePair<string, object> kvp in delta.DeltaProperties)
            {
                parameters[i] = CreateSqlParameter(kvp.Value, kvp.Key);

                ++i;
            }

            foreach (KeyValuePair<string, object> kvp in delta.Conditions)
            {
                parameters[i] = CreateSqlParameter(kvp.Value, kvp.Key);

                ++i;
            }

            return parameters;
        }

        /// <summary>
        /// 运行SQL文件
        /// </summary>
        /// <param name="fileName">SQL文件名</param>
        /// <returns></returns>
        public static int ExecuteSqlFile(string fileName)
        {
            StreamReader reader = new StreamReader(fileName);
            StringBuilder sqls = new StringBuilder();
            
            while (!reader.EndOfStream)
            {
                sqls.AppendLine(reader.ReadLine());
            }

            using (DBHelper helper = new DBHelper())
            {
                return helper.ExecuteNonQuery(sqls.ToString());
            }
        }
        #endregion

        #region Private functions
        /// <summary>
        /// 创建相等语句片段
        /// </summary>
        /// <param name="dic"></param>
        /// <returns></returns>
        private static string CreateEqualsSql(Dictionary<string, object> dic)
        {
            string sql = string.Empty;

            foreach (object key in dic.Keys)
            {
                sql += string.Format(" {0} = @{0} ,", key);
            }
            sql = sql.Substring(0, sql.Length - 1);

            return sql;
        }
        #endregion
    }
}
